﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Data.OleDb;

namespace DataLeakCop
{
    public class DBOperator
    {
        private String _connectionString = String.Empty;

        public DBOperator(String connectionString)
        {
            _connectionString = connectionString;
        }

        /// <summary>
        /// Read data based on the input query command.
        /// </summary>
        /// <param name="commandText">SQL Statement based on SQL 2008</param>
        /// <returns></returns>
        /// <exception cref="">Throw exception while reading.</exception>
        public DataTable Read(string commandText)
        {
            OleDbDataReader reader = null;
            DataTable dt = new DataTable();
            OleDbCommand command = new OleDbCommand();

            OleDbConnection connection = DBConnectionOperator.OpenConnection(_connectionString);
            command.Connection = connection;
            command.CommandText = commandText;

            using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(reader);
            }

            return dt;
        }

        /// <summary>
        /// Insert, update, delete, create, drop.
        /// </summary>
        /// <param name="commandText">SQL Statement based on SQL 2008</param>
        /// <returns> return true if update successfully. </returns>
        /// <exception cref="">Throw exception while reading.</exception>
        public bool Update(String commandText)
        {
            bool bUpdateSucceec = false;
            OleDbCommand command = new OleDbCommand();
            OleDbConnection connection = null;
            
            try
            {
                connection = DBConnectionOperator.OpenConnection(_connectionString);

                command.Connection = connection;

                command.CommandText = commandText;
                command.ExecuteNonQuery();
                bUpdateSucceec = true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                bUpdateSucceec = false;
            }
            finally
            {
                // Close the connection at the end
                if (connection != null)
                {
                    connection.Close();
                }
            }

            return bUpdateSucceec;
        }
    }
}
